Imports MyClasses
Imports System.Data.SqlClient


Public Class DSalePrint
    Public Shared Function GetCustomerData(ByVal CustID As Integer) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT MP.ITEMID,MP.SEQITEMID,MP.ITEMNAME,M.DESCRIPTION AS COMPANYNAME, " & _
                     "L.DESCRIPTION AS LOCATIONNAME,TD.SCHEMEID, TD.SCHEMEITEMQTY, TD.CLRcode, TD.QTY, " & _
                     "TD.SALEPRICE, TD.AMOUNT, TS.STATUS, " & _
                     "CAST(MONTH(TD.EXPDATE)AS VARCHAR) + '/' + CAST(YEAR(TD.EXPDATE)AS VARCHAR) AS EXPDATE, " & _
                     "FROM TSALE TS INNER JOIN MCUSTOMER C ON TS.CUSTID=C.CUSTID  " & _
                     "INNER JOIN TSALEDET -TD ON TS.SALEID=TD.SALEID  " & _
                     "INNER JOIN MPITEM MP ON TD.ITEMID=MP.ITEMID " & _
                     "INNER JOIN TSTOCK T ON TD.ITEMID=T.ITEMID " & _
                     "INNER JOIN MMANUFACTURER M ON M.MFCTRID=MP.COMPANYID " & _
                     "INNER JOIN MLOCATION L ON L.LOCATIONID=MP.LOCATIONID AND C.CUSTID=" & Convert.ToInt32(CustID)
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCounterData", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetCustomerPrintBills(ByVal CustID As Integer) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT  SaleID,SeqSaleID AS VoucherNo,SaleDate,(Amount+TotalTaxes),Discount,ISNULL(SUM (tPaymentDet.Amt),0) AS PaidAmount," & _
                     "(Amount+TotalTaxes -ISNULL(SUM (tPaymentDet.Amt),0)-Discount) AS Balance " & _
                     "FROM tSale LEFT OUTER JOIN tPaymentDet ON tSale.SaleID=tPaymentDet.BillID " & _
                     "LEFT OUTER JOIN tPayment ON tPayment.PayID=tPaymentDet.PayID  AND tPayment.BillType='Sale'  " & _
                     "WHERE  FinalBillID='0' AND CustID=" & CustID & _
                     " GROUP BY SaleDate,SaleID,SeqSaleID,Amount,Discount,GrossAmount,PaidAmount,TotalTaxes"
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCustomerPrintBills", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetCustomerFinalBills(ByVal CustID As Integer) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT  SaleID,SeqSaleID AS VoucherNo,SaleDate,Amount+TotalTaxes,Discount,SUM (tPaymentDet.Amt) AS PaidAmount," & _
                     "GrossAmount-PaidAmount AS Balance " & _
                     "FROM tSale  " & _
                     "INNER JOIN tPayment ON tPayment.PayID=tPaymentDet.PayID  " & _
                     "LEFT OUTER JOIN tPaymentDet ON tSale.SaleID=tPaymentDet.BillID " & _
                     "WHERE  FinalBillID <> '0' AND CustID=" & CustID & " AND tPayment.BillType='Sale' " & _
                     "GROUP BY SaleDate,SaleID,SeqSaleID,Amount,Discount,GrossAmount,PaidAmount,TotalTaxes"
            Return DbHelper.ExecuteDataTable(strSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCustomerFinalBills", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetBillItems(ByVal SaleID As Integer) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT SeqItemID,ItemName,mfctrShortName,CLRcode,CAST(MONTH(D.EXPDATE)AS VARCHAR) + '/' + CAST(YEAR(D.EXPDATE)AS VARCHAR) AS EXPDATE," & _
                     "SchemeID,SchemeItemQty,Qty,D.SalePrice,Amount " & _
                     "FROM  tSaleDet D LEFT OUTER JOIN mpItem I ON D.ItemID=I.ItemID " & _
                     "LEFT OUTER JOIN mManufacturer M ON M.mfctrID=I.mfctrID " & _
                     "WHERE SaleID=" & SaleID
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetBillItems", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetDoctor(ByVal CustID As String) As DataTable
        Try
            Dim StrSql As String
            StrSql = "SELECT MPDOCTOR.DOCTORID,MCITY.DESCRIPTION,MCUSTOMER.CUSTID " & _
                     "FROM MPDOCTOR ,MPERSON,MCUSTOMER,MCITY " & _
                     "WHERE MPDOCTOR.PERSONID = MPERSON.PERSONID " & _
                     "AND MPERSON.PERSONID = MCUSTOMER.PERSONID AND MCITY.CITYCD=MCUSTOMER.CITYCD " & _
                     "AND MPDOCTOR.STATUS='A' AND MCUSTOMER.CUSTID=" & Convert.ToInt32(CustID)

            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DDoctor", "GetDoctorList")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetPrintCriteria(ByVal PrintType As String) As DataTable
        Try
            Dim StrSql As String
            StrSql = "SELECT Height,Width,TopM,BottomM,LeftM,RightM " & _
                     "FROM MLAYOUTSET WHERE TYPE=" & PrintType

            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DDoctor", "GetDoctorList")
            Return Nothing
        End Try
    End Function

    '@20072412 : Add new function to get Customer Name
    Public Shared Function GetCustomerName(ByVal CustId As Integer) As DataTable
        Dim strCustomer As String
        Try
            strCustomer = "SELECT  custid,title,Fname+' '+Mname+' '+Lname AS Name" & _
                          " FROM mcustomer C,mperson P" & _
                          " WHERE C.personid=P.personid AND custid=" & CustId & ""
            Return DbHelper.ExecuteDataTable(strCustomer)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "Customer", "GetCustomerName")
            Return Nothing
        End Try
    End Function

    '@20072412 : Add new function to get Doctor Name
    Public Shared Function GetDoctorList(ByVal DoctID As Integer) As DataTable
        Try
            Dim StrSql As String
            StrSql = "SELECT DoctorID,Fname+' '+Mname+' '+Lname AS Name" & _
                     " FROM mpdoctor, mPerson " & _
                     " WHERE mpdoctor.PersonID = mPerson.PersonID AND DoctorID= " & DoctID & " AND Status='A' "
            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "Doctor", "GetDoctorList")
            Return Nothing
        End Try
    End Function


    'Written By:-(2008/01/03) :- Selecting The FooterInfo 
    Public Shared Function FooterInfo() As DataTable
        Dim StrSql As String = String.Empty
        Try
            StrSql = "SELECT * FROM MPREFS"

            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DGeneral", "FirmInfo", "")
            Return Nothing
        End Try
    End Function
End Class
